package com.hwua.dao.impl;

import com.hwua.dao.ScoreDao;
import com.hwua.entity.Score;
import com.hwua.entity.Student;
import com.hwua.entity.Teacher;
import com.hwua.util.DBHelper;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author CQY
 */
public class ScoreDaoImpl implements ScoreDao{
    @Override
    public int save(Score score) {
        String sql = "insert into score(scoreid,stuid,courseid,score) values(?,?,?,?)";
        Object[] params = {score.getSoureIn(),score.getStuId(),score.getCourseId(),score.getScore()};
        int row = 0;
        try {
            row = DBHelper.update(sql, params);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return row;
    }

    @Override
    public int update(Score score) {
        String sql = "update score set courseid=?,score=? where scoreid=?";
        Object[] params = {score.getCourseId(),score.getScore(),score.getSoureIn()};
        int row = 0;
        try {
            row = DBHelper.update(sql, params);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return row;
    }

    @Override
    public int delete(int id) {
        String sql = "delete from score where scoreid=?";
        Object[] params = {id};
        int row = 0;
        try {
            row = DBHelper.update(sql, params);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return row;
    }

    public int deleteByStuId(String id) {
        String sql = "delete from score where stuid=?";
        Object[] params = {id};
        int row = 0;
        try {
            row = DBHelper.update(sql, params);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return row;
    }


    @Override
    public int deleteBatch(int[] id) {
        int row=0 ;
        Connection conn=DBHelper.getConnection();
        String sql = "delete from score where scoreid=?";
        try {
            PreparedStatement pre=conn.prepareStatement(sql);
            for(int i=0;i<id.length;i++){
                pre.setInt(1,id[i]);
                //添加参数到批处理
                pre.addBatch();
            }
            //执行批处理
            int [] rows= pre.executeBatch();
            //统计删除行数
            for (int i = 0; i <rows.length ; i++) {
                row+=rows[i];
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return row;
    }

    @Override
    public Score findById(String id) {
        Connection conn = DBHelper.getConnection();
        Score score = null;
        String sql = "select * from score where stuid=?";
        try {
            PreparedStatement pre = conn.prepareStatement(sql);
            pre.setString(1, id);
            ResultSet rs = pre.executeQuery();
            if (rs.next()) {
                score = new Score();
                score.setStuId(rs.getString("stuid"));
                score.setCourseId(rs.getString("score"));
                score.setScore(rs.getInt("score"));

            }
            rs.close();
            pre.close();
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return score;
    }

    @Override
    public List<Score> findAll() {
        Connection conn = DBHelper.getConnection();
        List<Score> list = new ArrayList<Score>();
        Score score = null;
        String sql = "select * from score  ";
        try {
            PreparedStatement pre = conn.prepareStatement(sql);
            ResultSet rs = pre.executeQuery();
            while (rs.next()) {
                score = new Score();
                score.setStuId(rs.getString("stuid"));
                score.setCourseId(rs.getString("courseid"));
                score.setScore(rs.getFloat("score"));
                list.add(score);

            }
            rs.close();
            pre.close();
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return list;
    }

    @Override
    public List<Score> findSearch(String keyword) {
        return null;
    }


    @Override
    public List<Map<Object,String>> findCorrelation() {
        Connection conn = DBHelper.getConnection();
        List<Map<Object,String>> mapList = new ArrayList<Map<Object, String>>();

        String sql = "select a.scoreid,s.STuid,s.STUNAME,a.SCORE,c.courseid,c.COURSENAME,t.TEANAME from STUDENT s,SCORE a,COURSE c,TEACHER t " +
                "where s.STUID=a.STUID and a.COURSEID=c.COURSEID and c.TEAID=t.TEAID order by scoreid asc";
        try {
            PreparedStatement pre = conn.prepareStatement(sql);
            ResultSet rs = pre.executeQuery();
            while (rs.next()) {
                Map map=new HashMap();
                map.put(1,rs.getInt("scoreid"));
                map.put(2,rs.getString("STUid"));
                map.put(3,rs.getString("STUNAME"));
                map.put(4,rs.getFloat("SCORE"));
                map.put(5,rs.getString("COURSeid"));
                map.put(6,rs.getString("COURSENAME"));
                map.put(7,rs.getString("TEANAME"));
                mapList.add(map);

            }
            rs.close();
            pre.close();
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return mapList;
    }
}
